Imports System.Data.SqlClient
Imports System.Data
Imports CAPA_Entidad
Imports CAPA_Utilitarios
Public Class ArticuloUbicacionDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function registrarArticuloUbicacion(ByVal au As ArticuloUbicacion) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "USP_RegistrarArticuloUbicacion"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", au.CIA)
                .Parameters.AddWithValue("@SUC", au.SUC_Codigo)
                .Parameters.AddWithValue("@ALM", au.ALM_Codigo)
                .Parameters.AddWithValue("@ARTCOD", au.ART_Codigo)
                .Parameters.AddWithValue("@UBICOD", au.UBI_Codigo)
                .Parameters.AddWithValue("@STOCK", au.UAR_Stock)
                .Parameters.AddWithValue("@ASIG", au.ASIG_UBI)
                .Parameters.AddWithValue("@ACT", au.UAR_Activo)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function actualizarArticuloUbicacion(ByVal au As ArticuloUbicacion) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "USP_ActualizarArticuloUbicacion"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", au.CIA)
                .Parameters.AddWithValue("@SUC", au.SUC_Codigo)
                .Parameters.AddWithValue("@ALM", au.ALM_Codigo)
                .Parameters.AddWithValue("@ARTCOD", au.ART_Codigo)
                .Parameters.AddWithValue("@UBICOD", au.UBI_Codigo)
                .Parameters.AddWithValue("@STOCK", au.UAR_Stock)
                .Parameters.AddWithValue("@ASIG", au.ASIG_UBI)
                .Parameters.AddWithValue("@ACT", au.UAR_Activo)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function obtenerListadoArticuloUbicacion(ByVal codCIA As String, ByVal codSUC As String, ByVal codALM As String, ByVal codART As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT * FROM VW_ArticuloUbicacion " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND ALM_Codigo = @ALM AND ART_Codigo = @ART"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@ALM", codALM)
                .Parameters.AddWithValue("@ART", codART)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function obtenerArticuloUbicacion(ByVal codCIA As String, ByVal codSUC As String, ByVal codALM As String, ByVal codART As String, ByVal codUBI As String) As ArticuloUbicacion
        Dim au As New ArticuloUbicacion("-1", "", "", "", "", "", "", "")
        Dim con As SqlConnection = conex.obtenerConexion()
        Try
            con.Open()
            Dim query As String = "SELECT * FROM VW_ArticuloUbicacion " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND ALM_Codigo = @ALM AND ART_Codigo = @ART AND UBI_Codigo = @UBI"
            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@ALM", codALM)
                .Parameters.AddWithValue("@ART", codART)
                .Parameters.AddWithValue("@UBI", codUBI)
            End With
        Catch ex As Exception
            au = Nothing
        Finally
            con.Close()
        End Try

        Return au
    End Function



    Function actualizarStockArtUbicacion(ByVal codART As String, ByVal codUBI As String, ByVal cantidad As Integer, ByVal cantPick As Integer, ByVal tipo As String) As Boolean
        Dim query As String = ""
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            If tipo = "B" Then
                query = "UPDATE ArticuloUbicacion " & _
                        "SET UAR_Stock = UAR_Stock - @cantPickeada, " & _
                        "ASIG_UBI = ASIG_UBI - @cantPickeada " & _
                        "WHERE ART_Codigo = @articulo " & _
                        "AND UBI_Codigo = @ubicacion"

                Dim comando As New SqlCommand(query, con)
                With comando
                    .CommandText = query
                    .Parameters.AddWithValue("@cantPickeada", cantPick)
                    .Parameters.AddWithValue("@articulo", codART)
                    .Parameters.AddWithValue("@ubicacion", codUBI)
                    .ExecuteNonQuery()
                End With
            ElseIf tipo = "U" Or tipo = "C" Then
                query = "UPDATE ArticuloUbicacion " & _
                        "SET UAR_Stock = UAR_Stock - @cantPickeada, " & _
                        "ASIG_UBI = ASIG_UBI - @cantPickeada " & _
                        "WHERE ART_Codigo = @articulo " & _
                        "AND UBI_Codigo = @ubicacion"
                Dim comando As New SqlCommand(query, con)
                With comando
                    .CommandText = query
                    .Parameters.AddWithValue("@cantPickeada", cantPick)
                    .Parameters.AddWithValue("@articulo", codART)
                    .Parameters.AddWithValue("@ubicacion", codUBI)
                    .ExecuteNonQuery()
                End With
            End If

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function verificarExistenciaUbicacion(ByVal codUbicacion As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT u.UBI_Descripcion " & _
                                  "FROM ArticuloUbicacion au INNER JOIN Ubicacion u " & _
                                  "ON u.CIA = au.CIA AND u.SUC_Codigo = au.SUC_Codigo " & _
                                  "AND u.ALM_Codigo = au.ALM_Codigo AND u.UBI_Codigo = au.UBI_Codigo " & _
                                  "WHERE au.CIA+au.SUC_Codigo+au.ALM_Codigo+au.UBI_Codigo = '" & codUbicacion & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function listarArticulosXUbicacion(ByVal codUbicacion As String, ByVal codOrden As String, ByVal flag As String) As DataSet
        Dim query As String = ""
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            If flag = "0" Then
                query = "SELECT a.ART_Codigo, a.ART_Descripcion, dadpu.DADPU_Codigo, " & _
                        "(dadpu.DADPU_Cantidad - SUM(ISNULL(dadpu2.DADPU_Cantidad,0))) AS Cantidad " & _
                        "FROM Documento_Proveedor_Almacen_Detalle dpad " & _
                        "INNER JOIN Documento_Almacen_Detalle dad " & _
                        "ON dpad.CIA_Almacen = dad.CIA AND dpad.SUC_Almacen = dad.SUC " & _
                        "AND dpad.TDOC_Almacen = dad.TDOC_Codigo AND dpad.DCA_Codigo = dad.DCA_Codigo " & _
                        "AND dpad.DAD_Codigo = dad.DAD_Codigo AND dad.DAD_Activo = '1' " & _
                        "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " & _
                        "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC " & _
                        "AND dadp.TDOC_Codigo = dad.TDOC_Codigo AND dadp.DCA_Codigo = dad.DCA_Codigo " & _
                        "AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " & _
                        "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " & _
                        "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo " & _
                        "AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo AND dadpu.DCA_Codigo = dadp.DCA_Codigo " & _
                        "AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " & _
                        "AND dadpu.UBI_Codigo = '0000' " & _
                        "LEFT JOIN Documento_Almacen_Relacion dar " & _
                        "ON dar.CIA_Ingreso = dadpu.CIA AND dar.SUC_Codigo_Ingreso = dadpu.SUC_Codigo " & _
                        "AND dar.TDOC_Codigo_Ingreso = dadpu.TDOC_Codigo AND dar.DCA_Codigo_Ingreso = dadpu.DCA_Codigo " & _
                        "AND dar.DADPU_CodigoIng = dadpu.DADPU_Codigo AND dar.DAR_Activo = '1' " & _
                        "LEFT JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu2 " & _
                        "ON dadpu2.CIA = dar.CIA_Salida AND dadpu2.SUC_Codigo = dar.SUC_Codigo_Salida " & _
                        "AND dadpu2.TDOC_Codigo = dar.TDOC_Codigo_Salida AND dadpu2.DCA_Codigo = dar.DCA_Codigo_Salida " & _
                        "AND dadpu2.DADPU_Codigo = dar.DADPU_CodigoSal " & _
                        "INNER JOIN Articulo a " & _
                        "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' " & _
                        "INNER JOIN UnidadMedida um " & _
                        "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " & _
                        "WHERE dpad.CIA_Proveedor = '01' AND dpad.SUC_Proveedor = '01' " & _
                        "AND dpad.TDOC_Proveedor = 'OC' AND dpad.DCP_Codigo = '" & codOrden & "' " & _
                        "GROUP BY a.ART_Codigo, a.ART_Descripcion, dpad.DCA_Codigo, " & _
                        "dadpu.DADPU_Codigo, um.UNM_Abreviatura, dadpu.DADPU_Cantidad " & _
                        "HAVING (dadpu.DADPU_Cantidad - SUM(ISNULL(dadpu2.DADPU_Cantidad,0))) > 0"
            ElseIf flag = "1" Then
                query = "SELECT a.ART_Codigo, a.ART_Descripcion, '' AS DADPU_Codigo " & _
                        "FROM Articulo a INNER JOIN " & _
                        "ArticuloUbicacion au ON a.ART_Codigo = au.ART_Codigo " & _
                        "WHERE au.CIA+au.SUC_Codigo+au.ALM_Codigo+au.UBI_Codigo = '" & _
                        codUbicacion & "' AND au.UAR_Activo = '1' " & _
                        "AND UAR_Stock > 0"
            End If

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds, "ArtUbicacion")

        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function obtenerStockUbicacionXArticulo(ByVal codCIA As String, ByVal codSUC As String, ByVal codALM As String, ByVal codUbicacion As String, ByVal codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT (au.UAR_Stock-au.ASIG_UBI) AS stock, a.ART_UM " & _
                                  "FROM Articulo a INNER JOIN " & _
                                  "ArticuloUbicacion au ON a.ART_Codigo = au.ART_Codigo " & _
                                  "WHERE au.CIA+au.SUC_Codigo+au.ALM_Codigo+au.UBI_Codigo = @UBI " & _
                                  "AND au.ART_Codigo = @ART " & _
                                  "AND au.UAR_Activo = '1'"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@UBI", codCIA & codSUC & codALM & codUbicacion)
                .Parameters.AddWithValue("@ART", codArticulo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function verificarExistenciaUbicacionXArticulo(ByVal codUbicacion As String, ByVal codArticulo As String) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT * " & _
                                  "FROM ArticuloUbicacion " & _
                                  "WHERE CIA + SUC_Codigo + ALM_Codigo + UBI_Codigo = '" & codUbicacion & "' " & _
                                  "AND ART_Codigo = '" & codArticulo & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count <> 0 Then
                valor = True
            End If
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function obtenerUbicacionesXArticulo(ByVal codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT u.UBI_Descripcion, au.UAR_Stock - au.ASIG_UBI AS Stock " & _
                                  "FROM ArticuloUbicacion au " & _
                                  "INNER JOIN Ubicacion u ON u.UBI_Codigo = au.UBI_Codigo AND u.UBI_Codigo <> '0000' " & _
                                  "WHERE au.ART_Codigo = '" & codArticulo & "' AND au.UAR_Stock - au.ASIG_UBI > 0"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

End Class
